CREATE VIEW [dbo].[vw_SubsidyTmpHavaleh]
AS
SELECT        SUM(CAST(price AS INT)) AS Amount, K_Code, SUM(CAST(KQty1 AS INT)) AS K_qty1, CAST(Price_Forosh AS INT) AS Price_Forosh, Id_Havaleh, Id, GoodsCode, UnitCode
FROM            (SELECT        '5' AS UnitCode, ' ' AS GoodsCode, dbo.TmpHavaleh.K_Code, dbo.TmpHavaleh.K_Qty1 AS KQty1, dbo.TmpHavaleh.Price_Forosh, dbo.TmpHeadH.Id_Havaleh, 
                                                    dbo.TmpHavaleh.K_Qty1 * dbo.TmpHavaleh.Price_Forosh AS price, dbo.TmpHeadH.Id
                           FROM            dbo.TmpHavaleh WITH (NOLOCK) INNER JOIN
                                                    dbo.TmpHeadH WITH (NOLOCK) ON dbo.TmpHeadH.Id_Havaleh = dbo.TmpHavaleh.Id_Havaleh AND dbo.TmpHavaleh.K_Code NOT IN
                                                        (SELECT        GoodsCode
                                                           FROM            dbo.SubsidyGoodsMapping)
                           UNION ALL
                           SELECT        '1' AS UnitCode, dbo.SubsidyGoodsMapping.GoodsCode, dbo.SubsidyGoodsMapping.SubsidyGoodsCode AS K_Code, TmpHavaleh_1.K_Qty1 * dbo.SubsidyGoodsMapping.weight AS KQty1, 
                                                    TmpHavaleh_1.Price_Forosh, TmpHavaleh_1.Id_Havaleh, TmpHavaleh_1.K_Qty1 * TmpHavaleh_1.Price_Forosh AS price, TmpHeadH_1.Id
                           FROM            dbo.TmpHavaleh AS TmpHavaleh_1 WITH (NOLOCK) INNER JOIN
                                                    dbo.TmpHeadH AS TmpHeadH_1 WITH (NOLOCK) ON TmpHeadH_1.Id_Havaleh = TmpHavaleh_1.Id_Havaleh INNER JOIN
                                                    dbo.SubsidyGoodsMapping ON dbo.SubsidyGoodsMapping.GoodsCode = TmpHavaleh_1.K_Code) AS t1
GROUP BY K_Code, Price_Forosh, Id_Havaleh, Id, GoodsCode, UnitCode